<%@ page language="java" contentType="application/vnd.ms-excel; name='report_1';charset=utf-8"
    pageEncoding="UTF-8" import="com.java.service.*,java.util.*,com.java.model.UserSession,com.java.util.*,java.text.SimpleDateFormat"%>
<%
response.setHeader("Content-Disposition", "inline; filename=report_1.xls"); // 파일 이름 지정
response.setHeader("Content-Description", "JSP Generated Data");
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Excel Download</title>
</head>
<%

UserSession usersession = (UserSession)session.getAttribute("user");
//String ent_id 			= (String)session.getAttribute("ent_id");

String connGroup 	= "";
String user_id 		= "";
if(usersession!=null){
	connGroup 	= CmmUtil.nvl(usersession.getConnGroup(),"0");
	user_id 	= CmmUtil.nvl(usersession.getUser_id(),"");
}

System.out.println("connGroup======>"+connGroup);
System.out.println("user_id======>"+user_id);

String line_number	= CmmUtil.nvl(request.getParameter("line_number"),"10");
String start_date = CmmUtil.nvl(request.getParameter("start_date"),"");
String close_date = CmmUtil.nvl(request.getParameter("close_date"),"");


java.util.Date date = new java.util.Date();
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");     
String today = formatter.format(date); 



//페이징 처리 Start
	//페이지 표시정보 받기 혹은 검색입력창
	String curr_page = request.getParameter("curr_page");
	if (curr_page == null || curr_page.equals(""))
		curr_page = "1";
	String one_page = request.getParameter("one_page");
	if (one_page == null || one_page.equals(""))
		one_page = "10";
	String one_group = request.getParameter("one_group");
	if (one_group == null || one_group.equals(""))
		one_group = "10";
	
	String total_count = CmmUtil.nvl(request.getParameter("total_count"),"0");
	
	//페이징 처리 End	
	
	
	HashMap map = new HashMap();
	HashMap map1 = null;
	ArrayList list = new ArrayList();
	
	if(!start_date.equals("") && !close_date.equals("")){
		CommonDaoService commonservice = new CommonDaoService("Com0201lp01Dao");
		map.put("connGroup",connGroup);
		map.put("user_id",user_id);		
		map.put("start_date",start_date);
		map.put("close_date",close_date);		
		map.put("page_row",total_count);
		map.put("total_row","0");
		map.put("page_no","0");
		
		list = (ArrayList)commonservice.commonDaoMethod(map);
		
		System.out.println(list);
	}
	
	int lect_amt_total = 0; //교육비
	int ret_amt_total = 0; //환급액
	int lect_sum_total = 0; //실비용
	
	int kisu_total = 0; //기수과정 수강인원
	int sang_total = 0; //상시과정 수강인원
	int lect_amt_sang_total = 0; //상시과정 교육비
	
	int stu_total = 0;
	
	
	if(list != null){
		if(list.size() > 0){
			total_count = (String)((HashMap)list.get(0)).get("total_row");
			
			if(((HashMap)list.get(0)).get("lect_amt_total").toString() != null){
				lect_amt_total = Integer.parseInt(((HashMap)list.get(0)).get("lect_amt_total").toString());
				ret_amt_total = Integer.parseInt(((HashMap)list.get(0)).get("ret_amt_total").toString());
				
				kisu_total = Integer.parseInt(((HashMap)list.get(0)).get("kisu_total").toString());
				sang_total = Integer.parseInt(((HashMap)list.get(0)).get("sang_total").toString());
				lect_amt_sang_total = Integer.parseInt(((HashMap)list.get(0)).get("lect_amt_sang_total").toString());
				
				stu_total = kisu_total + sang_total;
				lect_sum_total = lect_amt_total - ret_amt_total;
			}
		}
	}
	
	String app_str="";	
	
%>

<body>
<form name="excel_frm">
<b></b>
</BR>
</BR>
       

<table width="760" border="1" cellpadding="2" cellspacing="0" bordercolor="C0C0C0" bordercolordark="#FFFFFF" frame=below >
                  <tr>
                    <td rowspan="2" bgcolor="#D3EBD7">교육기간</td>
                    <td rowspan="2" bgcolor="#D3EBD7">수강인원</td>
                    <td colspan="4" bgcolor="#D3EBD7">환급과정</td>
                    <td colspan="2" bgcolor="#D3EBD7">비환급과정</td>
                    <td rowspan="2" bgcolor="#D3EBD7">총교육비(a+b)</td>
                  </tr>
                  <tr>
                    <td class="table_td2Line">인원수</td>
                    <td class="table_td2Line">교육비(a)</td>
                    <td class="table_td2Line">환급액</td>
                    <td class="table_td2Line">실비용</td>
                    <td class="table_td2Line">인원수</td>
                    <td class="table_td2Line">교육비(b)</td>
                  </tr>
			<%if(list.size() != 0){ %>	      
                  
                  <tr>
                    <td><%=start_date %> ~ <%=close_date %></td>
                    <td><%=stu_total %></td>
                    <td><%=kisu_total %></td>
                    <td><%=lect_amt_total %></td>
                    <td><%=ret_amt_total %></td>
                    <td><%=lect_sum_total %></td>
                    <td><%=sang_total %></td>
                    <td><%=lect_amt_sang_total %></td>
                    <td><%=lect_amt_total + lect_amt_sang_total %></td>
                  </tr>
			<%} %>
</table>
<table><tr><td></td></tr><tr><td></td></tr></table>
<%if(list.size() > 0){ %>        
<table width="760" border="1" cellpadding="2" cellspacing="0" bordercolor="C0C0C0" bordercolordark="#FFFFFF" frame=below >
                      <tr>
                        <td rowspan="2" bgcolor="#D3EBD7">no.</td>
                        <td rowspan="2" bgcolor="#D3EBD7">이름</td>
                        <td rowspan="2" bgcolor="#D3EBD7">부서</td>
                        <td rowspan="2" bgcolor="#D3EBD7">직위</td>
                        <td rowspan="2" bgcolor="#D3EBD7">총과정</td>                        
                        <td colspan="5" bgcolor="#D3EBD7">환급</td>
                        <td colspan="3" bgcolor="#D3EBD7">비환급</td>
                        <td rowspan="2" bgcolor="#D3EBD7">총교육비(a+b)</td>						
                      </tr>
                      <tr>
                        <td class="table_td2Line">과정수</td>
                        <td class="table_td2Line">수료/미수료</td>
                        <td class="table_td2Line">환급액</td>
                        <td class="table_td2Line">실비용</td>
                        <td class="table_td2Line">교육비(a)</td>
                        <td class="table_td2Line">과정수</td>
                        <td class="table_td2Line">수료/미수료</td>
                        <td class="table_td2Line">교육비(b)</td>
                      </tr>
			<%
			
				for(int i=0; i<list.size(); i++){
            		map1 			= (HashMap)list.get(i);            	
            	
            	
			%>
                  	 <tr>                        
                        <td><%=map1.get("r_num") %></td>
                        <td><%=map1.get("login_nm") %></td>
                        <td><%=map1.get("dept_nm") %></td>
                        <td><%=map1.get("class_nm") %></td>
                        <td><%=map1.get("member_cnt") %></td>
                        <td><%=Integer.parseInt(map1.get("fin_y_cnt").toString()) +  Integer.parseInt(map1.get("fin_n_cnt").toString())%></td>
                        <td><%=map1.get("fin_y_cnt") %>/<%=map1.get("fin_n_cnt") %></td>
                        <td><%=map1.get("ret_amt") %></td>
                        <td><%=map1.get("lect_sum") %></td>
                        <td><%=map1.get("lect_amt") %></td>
                        <td><%=Integer.parseInt(map1.get("fin_y_sang_cnt").toString()) +  Integer.parseInt(map1.get("fin_n_sang_cnt").toString())%></td>
                        <td><%=map1.get("fin_y_sang_cnt") %>/<%=map1.get("fin_n_sang_cnt") %></td>
                        <td><%=map1.get("lect_amt_sang") %></td>
                        <td><%=Integer.parseInt(map1.get("lect_amt").toString()) +  Integer.parseInt(map1.get("lect_amt_sang").toString()) %></td>
                      </tr>
			<%
				} 			
			%>
                  
</table>   
                
<%} %>
</form>
</body>
</html>
